﻿USE [Northwind]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetTerritoriesFilterByDescription]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetTerritoriesFilterByDescription]
GO

CREATE PROCEDURE [dbo].[GetTerritoriesFilterByDescription]
	 @TerritoryDescription nchar(50) 
AS
	SET NOCOUNT ON;

	SELECT	Region.RegionID, Region.RegionDescription, 
			Territories.TerritoryID, Territories.TerritoryDescription 
	FROM Region inner join Territories   
	on Territories.RegionId = Region.RegionId 
	WHERE Territories.TerritoryDescription LIKE @TerritoryDescription;

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetTerritoriesByRegionId]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetTerritoriesByRegionId]
GO

CREATE PROCEDURE [dbo].[GetTerritoriesByRegionId]
	 @RegionId int 
AS
	SET NOCOUNT ON;

	SELECT	Region.RegionID, Region.RegionDescription, 
			Territories.TerritoryID, Territories.TerritoryDescription 
	FROM Region inner join Territories   
	on Territories.RegionId = Region.RegionId 
	WHERE Territories.RegionId = @RegionId;

GO
